لیست کشویی در اکسل ؛ ساده ترین تا پیشرفته ترین نکات کار با آن
فهرست محتوا
چطور می توانیم یک لیست کشویی ایجاد کنیم ؟
در اولین قدم با هم به سراغ روش های ایجاد یک لیست کشویی در اکسل می رویم .
صحبت از روش ها کردم ؛ نه روش . پس متوجه می شویم که برای ایجاد یک لیست فرو افتادنی یا همان لیست کشویی ما چند تا راه داریم .
اینجا با هم همه آنها را بررسی می کنیم .
روش اول : استفاده از Data validation
این متدوال ترین روشی است که به کمک ان می شود یک لیست کشویی را برای خودمان ایجاد کنیم . مراحل انجام این کار را با هم دنبال می کنیم .
1- اول سلول یا محدوده ای از سلول ها که می خواهید در داخل آن یک لیست کشویی تعریف کنید را انتخاب کنید .
2- حالا به سراغ سربرگ Data رفته و در گروه دستورات Data Tools و گزینه Data Validation را انتخاب کنید .
بعد از این کار یک پنجره ای به شما نمایش داده می شود . ادامه کار را در داخل این پنجره با هم دنبال می کنیم .
بعنوان اولین تنظیم کادر Allow را روی گزینه List تنظیم کنید . حالا باید به سراغ کادر source بروید .
اصل کار اینجا می گذرد . توی این کادر باید یک منبع، برای لیست فرو افتادنی خودتان به اکسل معرفی کنید .
برای این کار روش های مختلفی وجود دارد که همه آنها را در داخل ویدئوی کوتاه زیر با هم مرور می کنیم . پس ویدئو زیر رو بیایید با هم ببینیم.
روش دوم: استفاده از امکانات Developer
Developer سربرگی نیست که بصورت پیش فرض برای شما توی اکسل نمایش داده شود . بلکه این خودتان هستید که باید آن را به محیط کاری اکسل اضافه کنید .
من قبلا توی مطلب 2 روش برای ایجاد و کار با کمبو باکس ها در اکسل راجع به نحوه ایجاد لیست های فرو افتادنی با استفاده از این روش صحبت کرده ام .
پس اگر می خواهید با این روش آشنا شوید کافی است که به مطلب بالا مراجعه کنید .
لیست کشویی داینامیک چیست ؟ چطور آن را ایجاد کنیم ؟
قبل از هر چیز بیایید ببینیم منظور از لیست کشویی داینامیک چیست ؟
لیست کشویی داینامیک یا لیست کشویی پویا یک لیست است که خودش می تواند خودش را بروز رسانی کند . یعنی چه .
بیایید با هم یک مثال را بررسی کنیم .
به تصویر زیر نگاه کنید .
توی تصویر بالا من یک لیست فرو افتادنی ایجاد کرده ام . اطلاعات موجود در سلول های “J1:J6” را به عنوان سورس این لیست معرفی کرده ام . تا به اینجا همه چیز خوب پیش می رود .
اما فرض کنید به مجموع شغل های موجود در این لیست قرار است 2 تا شغل جدید دیگر هم اضافه کنیم . این کار را خیلی راحت در داخل سلول ها انجام می دهیم . اما آیا این شغل های جدید در داخل لیست فروافتادنی هم نمایش داده می شوند ؟ پاسخ خیر است ؟
پس چطور باید این اطلاعات جدید را در لیست نمایش بدهیم ؟ برای این کار سه تا راه وجود دارد :
راه اول این است که از همان اول محدوده سلول های مورد نظر خودمان را برای سورس لیست باکس بیشتر از سلول هایی تعریف کنیم که حاوی اطلاعات هستند . یعنی چی ؟
مثلا توی مثال بالا به جای محدوده “J1:J6” بیاییم محدوده “J1:J10” را به عنوان سورس لیست فروافتادنی خودمان معرفی کنیم .
اینجوری هر وقت اطلاعات جدیدی را در این محدوده وارد کنیم بصورت خودکار در داخل لیست فرو افتادنی ما هم نمایش داده می شود . این روش روش خوبی است اما یک ایراد کوچک هم دارد که در ویدئوی این بخش راجع به آن صحبت می کنم .
اما راه دوم این است که مجددا بیاییم و لیست فرو افتادنی خودمان را از ابتدا و این بار با محدوده جدید ایجاد کنیم . این هم روش خوبی می تواند باشد . اما روش بهتری هم وجود دارد .
روش سوم این است که بیاییم کاری کنیم که لیست فرو افتادنی ما یک حالت پویا داشته باشد یعنی بتواند خودش؛ اطلاعاتش را بروز رسانی کند . می پرسید چطوری می شود این کار را کرد .
برای این کار باید از تابعی به اسم offset و countA استفاده کنیم . اما چطوری ؟
به تصویر زیر نگاه کنید .
خواندن این مطلب را هم از دست ندهید : چطور در اکسل لیست های کشویی مرتبط به هم را ایجاد کنیم ؟
حالا ما یک لیست فرو افتادنی پویا داریم که هر مقدار جدیدی را که به محدوده مورد نظرمان اضافه شود بطور خودکار در داخل این لیست هم نمایش داده می شود .
آیا می شود یک مقدار را به لیست فروافتادنی اضافه کرد ؟
اگر متوجه سوال شده اید که پاسخ شما یک کلمه است . بله این کار شدنی است . اما برای کسانی که متوجه سوال نشده اند یک توضیح کوتاه بدهم .
همان مثال قبلی را در نظر بگیرید . فرض کنید که کاربر لیست کشویی ما را باز می کند و می خواهد یک عنوان شغلی را انتخاب کند . اما عنوان شغلی مورد نظر خودش را در این لیست پیدا نمی کند .
اینجا چه کار می کند . کاربر عنوان شغلی مورد نظر خودش را در داخل کادر لیست فروافتادنی تایپ می کند . اکسل به کاربر می گوید که همچین عنوانی در داخل این لیست وجود ندارد . پس از کاربر سوال می کند که آیا می خواهد این عنوان شغلی را به لیست فروافتادنی اضافه کند یا نه ؟
اگر کاربر تائید کند اکسل این عنوان جدید را به لیست اضافه می کند .
برای این کار باید به سراغ برنامه نویسی وی بی ای در اکسل برویم و یک سری از کدها را بنویسیم .
من اول کدهایی را که لازم داریم را در اینجا می آورم و بعد توضیح آنها را در ویدئوی ابتدای همین صفحه برایتان میگذارم .
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NewData As String
If Target.Count > 1 Then
Exit Sub
End If
If Not Intersect(Target, Range("G2:G15")) Is Nothing Then
NewData = ""
NewData = Target.Value
If WorksheetFunction.CountIf(Range("jobs"), NewData) = 0 Then
Range("jobs").End(xlDown).Offset(1, 0) = NewData
Range("jobs").Resize(Range("jobs").Rows.Count + 1, 1).Name = "jobs"
End If
End If
End Sub
کدهایی که لازم دارین رو در قسمت بالا نوشتیم .حالا بریم به سراغ اینکه چه جوری و کجا باید از این کدها استفاده کنیم تا به آن نتیجه دلخواه خودمان برسیم .
برای این کار کافی است که ویدئوی زیر را تا آخر ببینید .
چه جوری یک لیست با قابلیت چند انتخاب داشته باشیم ؟
توی حالت عادی همه لیست های فرو افتادنی که ایجاد می کنیم فقط امکان انتخاب یک مورد را به ما می دهند .
اما بعضی از مواقع هست که ما احتیاج داریم که بیشتر از یک آیتم را از لیست مورد نظرمان انتخاب کنیم .
اجازه بدهید یک مثال را با هم بررسی کنیم . فرض کنید که یک لیستی داریم از حضور افراد مختلف که در طی روزهای هفته باید در محل خاصی حضور داشته باشند .
در حالت عادی کاری که می کنیم برای ایام هفته یک لیست فرو افتادنی ایجاد می کنیم و می توانیم از بین روزهای هفته یک روز خاص را برای هر فرد انتخاب کنیم .
اما مشکل جایی بروز می کند که یک یا چند نفر از این افراد نه در یک روز بلکه در دو یا چند روز باید برایشان حضور در نظر بگیریم .
اینجا این لیست فرو افتادنی یا همان لیست کشویی ما را دچار محدودیت می کند چرا که در حالت عادی نمی توانیم بیشتر از یک روز را انتخاب کنیم .
اما ما در اینجا می خواهیم این محدودیت را برای خودمان از بین ببریم . می پرسید چطوری ؟ با من در ادامه همراه باشید تا برایتان توضیح بدهم .
مراحل ایجاد یک فهرست کشویی چند انتخابی
برای شروع کار این تصویر را در نظر بگیرید .
در تصویر بالا در داخل سلول D2 یک لیست کشویی داریم . شما هم باید یک لیست کشویی برای خودتان ایجاد کنید . اگر می پرسید چطور باید این کار را کرد می توانم شما را به مطلب زیر راهنمایی کنم .
مطلب مرتبط : 2 روش برای ایجاد و کار با کمبو باکس ها در اکسل
اما می خواهیم کاری کنیم که مثل تصویر بالا قابلیت چند انتخابی را به این لیست اضافه کنیم .
برای این کار چند تا مرحله را باید پشت سر بگذاریم . این مراحل شامل موارد زیر است :
اول با استفاده از دکمه های ALT+F11 به محیط کد نویسی اکسل وارد شوید .
در داخل این محیط به سراغ سربرگ insert رفته و گزینه module را انتخاب کنید . با این کار یک ماژول ایجاد و در اختیار شما قرار می گیرد .
حالا کدهای زیر را در داخل این ماژول قرار دهید .
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim f_data As String
Dim New_data As String
On Error GoTo Exitsub
If Target.Address = "$D$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
New_data = Target.Value
Application.Undo
f_data = Target.Value
If f_data = "" Then
Target.Value = New_data
Else
Target.Value = f_data & ", " & New_data
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
باور بکنید یا نه اما با وارد کردن این کدها در محیط کد نویسی اکسل شما یک لیست کشویی با قابلیت چند انتخابی را برای خودتان ایجاد کرده اید . کافی است به محیط اکسل رفته و این قضیه را امتحان کنید .
لیست انتخابی بدون تکرار
توی مرحله قبل با هم یک لیست کشویی را ایجاد کردیم که در آن واحد می شود چند تا آیتم را در داخل آن انتخاب کرد . اما چیزی که هست این آیتم ها می توانند حالت تکراری داشته باشند .
اما اگر برایتان مهم است که یک لیست کشویی داشته باشید که در داخل آن همه آیتم هایی که انتخاب می کنید کاملا حالت غیر تکراری داشته باشند باید کدهای قبلی را پاک کنید .
حالا برای این کار از کدهای زیر به جای کدهای قبلی استفاده کنید .
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pvalue As String
Dim nvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
nvalue = Target.Value
Application.Undo
pvalue = Target.Value
If pvalue = "" Then
Target.Value = nvalue
Else
If InStr(1, pvalue, nvalue) = 0 Then
Target.Value = pvalue & ", " & nvalue
Else:
Target.Value = pvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
فقط یادتان باشد که این کدها را باید در داخل رویداد change مربوط به ورک شیت مورد نظرتان قرار بدهید تا با هر بار تغییری که در داخل لیست کشویی شما ایجاد می شود این کدها اجرا شوند .
حالا دیگر در داخل لیست کشویی تان امکان ایجاد و استفاده از داده های تکراری در آن وجود ندارد .
اما یک سوال این لیست فقط مربوط به یک سلول است چطوری می شود کاری کرد که این لیست برای کل یک ستون مثلا ستون c نمایش داده شود ؟
خوب برای این کار کافی است که در داخل کد خودتان عبارت If Target.Address = “$D$2” Then را پیدا کنید .
حالا به جای آن خط زیر را جایگزین آن کنید .
If Target.Column = 4 Then
اینجا از برنامه می خواهیم که این لیست را برای کل ستون شماره 4 یعنی همان ستون D در نظر بگیرد .
این آموزش هم در اینجا با اتمام می رسد .
امیدوارم که این مطلب هم برایتان مفید و کاربردی بوده باشد . نظر خودتان را در مورد این مطلب در بخش دیدگاهها ثبت کنید .
مطالب زیر را حتما مطالعه کنید
چطور زبان ورود اطلاعات در اکسس را کنترل کنیم ؟
با رعایت این نکات تبدیل به یک برنامه نویس حرفه ای در اکسل شوید .
۷ نکته مهم در برنامه نویسی VBA که باید آنها را بدانید
ماکرو نویسی در اکسل |ببینید که ماکروها چقدر کاربردی اند.
زبان وی بی ای مرده است
۱۰ کاری که بدون برنامه نویسی VBA قادر به انجامش نبودید .
توی این آموزش کارهایی رو بررسی می کنیم که فقط با استفاده از کدهای وی بی ای می شود آنها را در آفیس انجام داد .
4 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
این که همش برنامه نویسه راهی نیست بدون کد نویسی این کار رو کرد به فکر ما هم باشید که از برنامه نویسی چیزی نمی دونیم
خیلی از کارها هستش که در محیط اکسس یا اکسل بدون کد نویسی امکان پذیر نیست
سلام،یک یوزرفرم دارم که یک کامبوباکس گذاشتم و میخوام که بتونم از کامبوباکس چند گزینه انتخاب کنم باید چیکار کنم؟
وقتی که کمبوباکس رو توی جدول طراحی می کنید گزینه Allow Multiple select رو تیک بزنید